﻿using MyAspx.EasyFramework.DAL;
using MyAspx.EasyFramework.EntityFramework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Data.Entity;
namespace WebUI.Form
{
    public partial class ActiveListCodes : System.Web.UI.Page
    {
        DBAccess db = new DBAccess();
        public commyaspxdbEntities _commyaspxdbEntities = new commyaspxdbEntities();
        protected void Page_Load(object sender, EventArgs e)
        {
            Title = "活跃榜单,Asp.net源码|- MyAspx.com";
            bindTop10ActiveCodes();
        }

        public List<string[]> bindTop10ActiveCodesByCommentNewDesc()
        {
            List<string[]> cur = new List<string[]>();
            //var maxcode = _commyaspxdbEntities.my_comment.GroupBy(gb => gb.codefileid).Select(s=>s.);
            SqlDataAdapter dba = new SqlDataAdapter(
            db.CreateCommand("select top 10 a.*,b.newtime,h.userid from my_fileinfo a  join (select codefileid,MAX(a.commenttime) as newtime from  (select codefileid,commenttime from my_comment  group by codefileid,commenttime ) a group by a.codefileid) as b on a.fileid = b.codefileid left join  (select fileid,userid from my_usercodererelation ) as h on h.fileid = a.fileid order by b.newtime desc"));
            DataSet ds = new DataSet();
            dba.Fill(ds);
            DataTable
                ndt = new DataTable();
            ndt = ds.Tables[0];
            foreach (DataRow dr in ndt.Rows)
            {
                string fid = dr["fileid"].ToString(); var salenum = "";
                if (_commyaspxdbEntities.my_saledetail.Where(f => f.fileid == fid).Count() > 0)
                {
                    salenum = _commyaspxdbEntities.my_saledetail.Where(f => f.fileid == fid).FirstOrDefault().salenum.ToString();
                }
                else { salenum = "免费"; }
                var userid = 0; var username = ""; var userlogimg = "";
                if (_commyaspxdbEntities.my_usercodererelation.Where(u => u.fileid == fid).Count() > 0)
                {
                    userid = int.Parse(
            _commyaspxdbEntities.my_usercodererelation.Where(u => u.fileid == fid).FirstOrDefault().userid.ToString());
                    username = _commyaspxdbEntities.my_users.Where(a => a.userid == userid).FirstOrDefault
              ().useregname;
                    userlogimg = _commyaspxdbEntities.my_users.Where(a => a.userid == userid).FirstOrDefault
                      ().uselogopic;
                }
                else
                {
                    username = "sys";
                    userlogimg = "/images/admin/login-user-logo.png";
                }

                idx += 1;
                string[] curarray = new string[] { 
                  dr["filefirimg"].ToString(),
                  dr["filevstype"].ToString(),
                       dr["filedatabase"].ToString(),

                            salenum.ToString(),
                                 dr["newtime"].ToString(),
                                     username.ToString(),
                                           userlogimg.ToString(),
                                           dr["filename"].ToString(),
                                           dr["usermsg"].ToString(),
                                           idx.ToString(),
                                           fid.ToString()
                };
                cur.Add(curarray);
            }
            return cur;
        }

        public List<string[]> bindTop15ActiveCodesByUploaderCommentDesc()
        {
            List<string[]> cur = new List<string[]>();
            //var maxcode = _commyaspxdbEntities.my_comment.GroupBy(gb => gb.codefileid).Select(s=>s.);
            SqlDataAdapter dba = new SqlDataAdapter(
            db.CreateCommand("select top 15 a.*,b.*,h.userid from my_fileinfo a  join (select * from (select a.*,b.userid  as suserid  from my_comment as a left join (select userid,fileid from my_usercodererelation) as b on a.codefileid =b.fileid  )gg where gg.suserid <> 0 and gg.userid =gg.suserid) b  on a.fileid = b.codefileid left join  (select fileid,userid from my_usercodererelation ) as h on h.fileid = a.fileid order by b.commenttime desc"));
            DataSet ds = new DataSet();
            dba.Fill(ds);
            DataTable
                ndt = new DataTable();
            ndt = ds.Tables[0];
            foreach (DataRow dr in ndt.Rows)
            {
                string fid = dr["fileid"].ToString(); var salenum = "";
                if (_commyaspxdbEntities.my_saledetail.Where(f => f.fileid == fid).Count() > 0)
                {
                    salenum = _commyaspxdbEntities.my_saledetail.Where(f => f.fileid == fid).FirstOrDefault().salenum.ToString();
                }
                else { salenum = "免费"; }
                var userid = 0; var username = ""; var userlogimg = "";
                if (_commyaspxdbEntities.my_usercodererelation.Where(u => u.fileid == fid).Count() > 0)
                {
                    userid = int.Parse(
            _commyaspxdbEntities.my_usercodererelation.Where(u => u.fileid == fid).FirstOrDefault().userid.ToString());
                    username = _commyaspxdbEntities.my_users.Where(a => a.userid == userid).FirstOrDefault
              ().useregname;
                    userlogimg = _commyaspxdbEntities.my_users.Where(a => a.userid == userid).FirstOrDefault
                      ().uselogopic;
                }
                else
                {
                    username = "sys";
                    userlogimg = "/images/admin/login-user-logo.png";
                }

                idx += 1;
                string[] curarray = new string[] { 
                  dr["filefirimg"].ToString(),
                  dr["filevstype"].ToString(),
                       dr["filedatabase"].ToString(),

                            salenum.ToString(),
                                 dr["addtime"].ToString(),
                                     username.ToString(),
                                           userlogimg.ToString(),
                                           dr["filename"].ToString(),
                                           dr["usermsg"].ToString(),
                                           idx.ToString(),
                                           fid.ToString(),
                                           dr["commenttime"].ToString(),
                                           dr["commentcontent"].ToString()
                };
                cur.Add(curarray);
            }
            return cur;
        }
        int idx = 0;
        public List<string[]> bindTop10ActiveCodes()
        {
            List<string[]> cur = new List<string[]>();
            //var maxcode = _commyaspxdbEntities.my_comment.GroupBy(gb => gb.codefileid).Select(s=>s.);
            SqlDataAdapter dba = new SqlDataAdapter(
            db.CreateCommand("select top 10 a.*,b.c,h.userid from my_fileinfo a  join (SELECT COUNT(*) as c,codefileid FROM [commyaspxdb].[dbo].[my_comment] group by codefileid)  as b on a.fileid = b.codefileid left join  (select fileid,userid from my_usercodererelation ) as h on h.fileid = a.fileid order by b.c desc"));
            DataSet ds = new DataSet();
            dba.Fill(ds);
            DataTable
                ndt = new DataTable();
            ndt = ds.Tables[0];
            foreach (DataRow dr in ndt.Rows)
            {
                string fid = dr["fileid"].ToString(); var salenum = "";
                if (_commyaspxdbEntities.my_saledetail.Where(f => f.fileid == fid).Count() > 0)
                {
                      salenum = _commyaspxdbEntities.my_saledetail.Where(f => f.fileid == fid).FirstOrDefault().salenum.ToString();
                }
                else { salenum = "免费"; }
                var userid = 0; var username ="";var userlogimg="";
                if (_commyaspxdbEntities.my_usercodererelation.Where(u => u.fileid == fid).Count() > 0)
                {
                    userid = int.Parse(
            _commyaspxdbEntities.my_usercodererelation.Where(u => u.fileid == fid).FirstOrDefault().userid.ToString());
                          username = _commyaspxdbEntities.my_users.Where(a => a.userid == userid).FirstOrDefault
                    ().useregname;
                  userlogimg = _commyaspxdbEntities.my_users.Where(a => a.userid == userid).FirstOrDefault
                    ().uselogopic;
                }
                else
                {
                    username = "sys";
                    userlogimg = "/images/admin/login-user-logo.png";
                }
       
                idx += 1;
                string[] curarray = new string[] { 
                  dr["filefirimg"].ToString(),
                  dr["filevstype"].ToString(),
                       dr["filedatabase"].ToString(),

                            salenum.ToString(),
                                 dr["c"].ToString(),
                                     username.ToString(),
                                           userlogimg.ToString(),
                                           dr["filename"].ToString(),
                                           dr["usermsg"].ToString(),
                                           idx.ToString(),
                                           fid.ToString()
                };
                cur.Add(curarray);
            }
            return cur;
        }
        public bool timeClass(string num)
        {
            //get
           // {
                string s = num;
                int x = int.Parse(s);
                if (x % 2 == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }  
            //}
        }

        public string Fitusername(string un)
        {
            if (un.Length > 10)
            {
                return un.Substring(0, 10);
            }
            else
                return un;
        }
    }
}